package com.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.servlet.My;

import hyl.core.MyFun;
import hyl.core.db.MyDB;
import hyl.core.db.MySqlSelect;
import hyl.core.db.MySqlSelect2;
import hyl.core.db.SelectWhere;
import hyl.core.info.Content;
import hyl.ext.base.MyToken;

public class SqlOfUser {
	private static final String select_user = "select a.id,a.tjrid,a.uname,a.uphone,a.roleid,a.deptid,a.ualias,a.state,a.ctime,b.tm1 headimg,b.i0 sex from user a,user_conf b where  a.id=b.id ";

	// 判断重复
	public static Integer isEffUid(String name) {

		String sql = "select id from user where  uname=?";
		String[] params = { name };
		MyDB db = My.getDB1();
		Integer m = (Integer) db.querySingle(sql, params);
		if (m == null)
			return 0;
		else
			return 1;
	}

	// 如果号码不存在返回
	public static Integer isEffTel(String num) {
		String sql = "select id from user where  uphone=? ";
		String[] params = { num };
		MyDB db = My.getDB1();
		Integer m = (Integer) db.querySingle(sql, params);
		if (m == null)
			return 0;
		else
			return 1;
	}

	// 取得所有的会员信息
	// 2019/4/16
	public static Map<String, Object> get所有会员列表(Integer 用户角色, Integer 报单员编号, int index, int size, String uid,
			String uname, String ualias, String start, String end, String 状态) {
		// System.out.println(状态);
		Content ct = new Content();

		String sql1 = "SELECT COUNT(1) AS total FROM USER a, user_ext b WHERE a.id=b.id ";

		String sql = "	SELECT a.*,e.uname ename,f.uname fname,g.uname gname FROM "
				+ "(SELECT a.id, a.uname, a.uphone, a.ualias, a.cip, a.state, a.ctime, a.umail, a.roleid, a.deptid, a.tjrid ,b.i0,b.i2,b.i3,b.i4,b.i5,b.c00,b.dt0,c.pid,b.l4,b.i1,c.area  "
				+ "FROM `user` a,user_ext b LEFT JOIN user_tree c ON b.id=c.uid  WHERE a.id=b.id {0}  ) a  "
				+ "LEFT JOIN `USER` e ON a.tjrid=e.id LEFT JOIN `USER` f ON a.pid=f.id "
				+ "LEFT JOIN `USER` g ON a.i1=g.id where 1=1 ";

		sql = sql + " {1} order by a.id desc {2}";
		MySqlSelect count = MySqlSelect.getInstance(sql1);
		MySqlSelect2 select = MySqlSelect2.getInstance(sql);

		SelectWhere sw0 = new SelectWhere();
		if (用户角色 != 1) {
			sw0.add("and (b.i1=?", 报单员编号);
			sw0.add("or a.id=?)", 报单员编号);
		}
		SelectWhere sw = new SelectWhere();
		sw.add("and a.id=? ", uid);
		sw.add("and substring(a.state,7,1)=? ", 状态);
		sw.add("and a.uname=? ", uname);
		sw.add("and a.ualias=? ", ualias);
		sw.add("and a.ctime>=? ", start);
		sw.add("and a.ctime<=? ", end);
		select.addParams(0, sw0);
		select.addParams(1, sw);
		count.addParams(sw0);
		count.addParams(sw);
		select.fromto(2, index, size);
		// System.out.println(sql);
		// System.out.println(sql1);
		ct.setCode(1, "查询成功");
		ct.iniBodys(count.getMap());
		ct.addBodyItem("_rs", select.getList2D());
		return ct.get();
	}

	// 读取用户信息
	public Map<String, Object> getUserByID(Integer userid) {
		String sql = select_user + " and a.id=? ";
		Object[] params = { userid };
		MyDB db = My.getDB1();
		return db.queryMap(sql, params);
	}

	public static Map<String, Object> getUserByNameID(String name, Integer userid) {
		String sql = select_user + "  and (a.id=? or a.uname=? )";
		Object[] params = { userid, name };
		MyDB db = My.getDB1();
		return db.queryMap(sql, params);
	}

	// 查询用户信息例子
	public static Map<String, Object> getUserList(String orderby, String uname, String start, String end, String state,
			int index, int size) {
		Content ct = new Content();
		MySqlSelect select1 = MySqlSelect.getInstance(select_user);
		MySqlSelect count1 = MySqlSelect.getInstance("select count(1) as total from user where 1=1 ");
		SelectWhere sw = new SelectWhere();
		sw.addLike("and (a.uname like ?", uname);
		sw.addLike("or a.uphone like ?)", uname);
		sw.add("and a.ctime >=? ", start);
		sw.add("and a.ctime <=? ", end);
		sw.add("and right(a.state,1)=?", state);
		count1.addParams(sw);
		select1.addParams(sw);
		select1.orderby(orderby);
		select1.fromto(index, size);
		ct.setCode(1, "查询成功");
		ct.iniBodys(count1.getMap());
		ct.addBodyItem("_rs", select1.getList());
		return ct.get();
	}

	public static Object is注册(String phone, String mail) {
		String sql = "select id from user a where  a.uphone=? or a.umail=? ";
		Object[] params = { phone, mail };
		MyDB db = My.getDB1();
		return db.querySingle(sql, params);
	}
	public static Object is注册邮箱(String mail) {
		String sql = "select id from user a where  a.umail=? ";
		Object[] params = {  mail };
		MyDB db = My.getDB1();
		return db.querySingle(sql, params);
	}
	// 读取用户信息
	public static Map<String, Object> login(String name, String phone, String pass) {
		// 最多保留10个备份
		String sql = select_user + " and (a.uname =? or a.uphone=?) and a.upass=? ";
		Object[] params = { name, phone, pass };
		MyDB db = My.getDB1();
		return db.queryMap(sql, params);
	}

	public static int reg(String 推荐人, String 登录名, String 手机, String 密码, String 邮箱, String ip) {
		String sql = "select id from user a where  a.uphone=? or a.umail=? ";
		Object[] params = { 推荐人, 推荐人 };
		MyDB db = My.getDB1();
		Integer tjrid = (Integer) db.querySingle(sql, params);
		tjrid = MyFun.nvlInt(tjrid, 0);
		return reg(tjrid, 登录名, 手机, 密码, 邮箱, ip);
	}

	/**
	 * 注册会员
	 */
	public static int reg(Integer 推荐人, String 登录名, String 手机, String 密码, String 邮箱, String ip) {
		MyDB db = My.getCommitDB1();
		// 最多保留10个备份
		String sql = "insert into user(ctime,tjrid,uname,uphone,upass,umail,cip) values (now(),?,?,?,?,?,?)";
		Object[] params = { 推荐人, 登录名, 手机, 密码, 邮箱, ip };
		Long i = db.executeUpdate(sql, params);
		sql = "insert into user_ext(id) values(" + i + ")";
		db.executeUpdate(sql);
		sql = "insert into user_conf(id) values(" + i + ")";
		db.executeUpdate(sql);
		sql = "insert into user_act(id) values(" + i + ")";
		db.executeUpdate(sql);
		db.commit();
		if (i > 0)
			return 1;
		else
			return 0;
	}

	/**
	 * 注册员工
	 */
	public static int reg(Integer 推荐人, String 登录名, String 手机, String 密码, String 邮箱, Integer 部门, Integer 角色, String ip) {
		// 最多保留10个备份
		String sql = "insert into user(ctime,tjrid,uname,uphone,upass,umail,cip,roleid,deptid)"
				+ " values (now(),?,?,?,?,?,?,?,?)";
		Object[] params = { 推荐人, 登录名, 手机, 密码, 邮箱, ip, 角色, 部门 };
		MyDB db = My.getCommitDB1();
		Long i = db.executeUpdate(sql, params);
		sql = "insert into user_ext(id) values(" + i + ")";
		db.executeUpdate(sql);
		sql = "insert into user_conf(id) values(" + i + ")";
		db.executeUpdate(sql);
		sql = "insert into user_act(uid) values(" + i + ")";
		db.executeUpdate(sql);

		db.commit();
		if (i > 0)
			return 1;
		else
			return 0;
	}

	public static int resetPass(String phone, String pass) {

		String sql = "update user set upass=? where  uphone=? ";
		Object[] params = { pass, phone };
		MyDB db = My.getDB1();
		Long i = db.executeUpdate(sql, params);
		if (i > 0)
			return 1;
		else
			return 0;
	}
	public static int resetPass_mail(String mail, String pass) {

		String sql = "update user set upass=? where  umail=? ";
		Object[] params = { pass, mail };
		MyDB db = My.getDB1();
		Long i = db.executeUpdate(sql, params);
		if (i > 0)
			return 1;
		else
			return 0;
	}
	// 审核更新状态 要么0 要么1
	public static Integer do审核(Integer uid) {
		MyDB db = My.getCommitDB1();
		String sql = "update user set state=concat(left(state,6),'1',right(state,1)) where id=?";
		Object[] params = { uid };
		db.executeUpdate(sql, params);
		sql = "update user_ext set dt0=now() where id=?";
		db.executeUpdate(sql, params);
		db.commit();
		return 1;
	}

	// 取得权限
	public static Map<String, Object> get菜单权限() {
		Content ct = new Content();
		MySqlSelect count = MySqlSelect.getInstance("select count(1) as total from sys_rolemanage a where 1=1");
		String sql = "select a.id,a.rolename,b.ss,a.manage from sys_rolemanage a left join (select count(*) ss,roleid from user group by roleid) b on a.id=b.roleid";
		MySqlSelect2 select = MySqlSelect2.getInstance(sql);
		ct.setCode(1, "查询成功");
		ct.iniBodys(count.getMap());
		ct.addBodyItem("_rs", select.getList2D());
		return ct.get();
	}

	// 取得当天会员
	public static Map<String, Object> get当日会员列表(Integer 角色, Integer 报单人id, int index, int size, String uname,
			String ualias) {

		Content ct = new Content();
		String sql1 = "select count(1) as total from user where DATE_FORMAT(ctime,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')";
		if (角色 != 1)
			sql1 = "select count(1) as total from user where id=-1";
		String sql = "SELECT uname,ualias,ctime,tjrid FROM USER a ,user_ext b WHERE a.id=b.id AND DATE_FORMAT(a.ctime,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d')  ";
		if (角色 != 1)
			sql = sql + "and (b.i1=" + 报单人id + " or a.id=" + 报单人id + ")";
		sql = sql + " {1}{2}";
		MySqlSelect count = MySqlSelect.getInstance(sql1);
		MySqlSelect2 select = MySqlSelect2.getInstance(sql);
		SelectWhere sw = new SelectWhere();

		if (uname != null && !uname.isEmpty()) {
			sw.add("and uname=? ", uname);
		}
		if (ualias != null && !ualias.isEmpty()) {
			sw.add("and ualias=? ", ualias);
		}
		select.addParams(1, sw);
		count.addParams(sw);
		select.fromto(2, index, size);
		ct.setCode(1, "查询成功");
		ct.iniBodys(count.getMap());
		ct.addBodyItem("_rs", select.getList2D());
		return ct.get();
	}

	// 会员资料（20190318）
	// 13.11
	public static Map<String, Object> get会员资料列表(Integer 角色, Integer 报单员编号, int index, int size, String uname,
			String ualias, String start, String end) {
		Content ct = new Content();
		String sql1 = "SELECT COUNT(1) AS total FROM  USER a,user_conf b ,USER e  WHERE a.id=b.id AND a.tjrid=e.`id` ";
		if (角色 != 1)
			sql1 = "SELECT COUNT(1) AS total FROM USER a,user_conf b ,USER e  WHERE a.id=b.id AND a.tjrid=e.`id`  AND (a.id="
					+ 报单员编号 + " OR b.i1=" + 报单员编号 + ")";

		String sql = "SELECT a.id,a.uname,a.ualias,f.i0,a.ctime,a.uphone,b.t0,b.t1,b.t2,b.ts0,e.`uname`,b.t4,b.t5,b.t6,b.ts1 FROM USER a,user_conf b ,USER e ,user_ext f  WHERE a.id=b.id AND a.tjrid=e.`id` AND a.id=f.id  ";
		if (角色 != 1)
			sql = sql + " and (b.i1=" + 报单员编号 + " or a.id=" + 报单员编号 + " )";
		sql = sql + " {1}{2}";
		MySqlSelect count = MySqlSelect.getInstance(sql1);
		MySqlSelect2 select = MySqlSelect2.getInstance(sql);
		SelectWhere sw = new SelectWhere();
		sw.add("and a.uname=? ", uname);
		sw.add("and a.ualias=? ", ualias);
		sw.add("and a.ctime>=? ", start);
		sw.add("and a.ctime<=? ", end);
		select.addParams(1, sw);
		count.addParams(sw);
		select.fromto(2, index, size);
		ct.setCode(1, "查询成功");
		ct.iniBodys(count.getMap());
		ct.addBodyItem("_rs", select.getList2D());
		return ct.get();
	}

	// 会员钱包
	public static Map<?, ?> get会员账户列表(Integer 角色, Integer 用户id, int index, int size, String uname, String ualias,
			String uid, String start, String end) {

		Content ct = new Content();
		String sql1 = "";
		if (角色 == 1)
			sql1 = "select count(1) as total from act_finance where  1=1 ";
		String sql = "SELECT a.uid,b.uname,b.ualias, a.l9,a.l6,a.l0,a.l4,a.l1 FROM act_finance a,USER b WHERE a.uid=b.id  ";
		if (角色 != 1)
			sql = sql + "and (b.id=" + 用户id + ")";
		sql = sql + " {1}{2}";
		MySqlSelect count = MySqlSelect.getInstance(sql1);
		MySqlSelect2 select = MySqlSelect2.getInstance(sql);
		SelectWhere sw = new SelectWhere();
		sw.add("and b.uname=? ", uname);
		sw.add("and b.ualias=? ", ualias);
		sw.add("and b.id=? ", uid);
		sw.add("and b.ctime>=? ", start);
		sw.add("and b.ctime<=? ", end);
		select.addParams(1, sw);
		count.addParams(sw);
		select.fromto(2, index, size);
		ct.setCode(1, "查询成功");
		ct.iniBodys(count.getMap());
		ct.addBodyItem("_rs", select.getList2D());
		return ct.get();
	}

	// 获得用户权限
	public static String get会员权限(HttpServletRequest request, HttpServletResponse response) {
		MyToken ss = My.getToken(request);
		if (ss.getUname() != null) {
			MyDB db = My.getDB1();
			String sql = "select a.* from sys_rolemanage a,user b where a.id=b.roleid and b.uname=?";
			Object[] param = { ss.getUname() };
			Map<?, ?> map = db.queryMap(sql, param);
			return map.get("manage").toString();
		}
		return "";
	}

	public static String close(Integer uid, String state) {
		MyDB db = My.getDB1();

		char[] result = state.toCharArray();
		if (result[7] == '0') {
			result[7] = '1';
		} else {
			result[7] = '0';
		}
		String ss = new String(result);
		db = My.getDB1();
		String sql = "update user set state=? where id=?";
		Object[] param1 = { ss, uid };
		db.executeUpdate(sql, param1);
		return "success";
	}

	public static String change密码(String password, String newpassword1, String newpassword2, String uname) {
		MyDB db = My.getDB1();
		String sql = "select * from user where uname=? and upass=?";
		Object[] param = { uname, password };
		List<?> list = db.queryList(sql, param);
		if (list == null || list.size() == 0) {
			return "error";
		} else {
			if (!newpassword1.equals(newpassword2)) {
				return "wrong";
			} else {
				db = My.getDB1();
				sql = "update user set upass = ? where uname=?";
				Object[] param1 = { newpassword1, uname };
				db.executeUpdate(sql, param1);
			}
		}
		return "success";
	}

	// SX
	public static void do审核树节点(Integer userid) {
		MyDB db = My.getDB1();
		String sql = "update user_tree set state=concat(left(state,6),'1',right(state,1)) where uid=?";
		Object[] params = { userid };
		db.executeUpdate(sql, params);

	}

	public static Map<Integer, String> getRoles() {
		MyDB db = My.getDB1();
		String sql = "select id,manage from sys_rolemanage a";
		Map<Integer, String> MRole = new HashMap<>();
		List<Map<String, Object>> list = db.queryList(sql, null);
		for (Map<String, Object> b : list) {
			MRole.put((Integer) b.get("id"), "," + (String) b.get("manage") + ",");
		}
		return MRole;
	}

	public static String changepassword(String password, String newpassword1, String newpassword2, String uname) {
		MyDB db = My.getDB1();
		String sql = "select * from user where uname=? and upass=?";
		Object[] param = { uname, password };
		List<?> list = db.queryList(sql, param);
		if (list == null || list.size() == 0) {
			return "error";
		} else {
			if (!newpassword1.equals(newpassword2)) {
				return "wrong";
			} else {
				db = My.getDB1();
				sql = "update user set upass = ? where uname=?";
				Object[] param1 = { newpassword1, uname };
				db.executeUpdate(sql, param1);
			}
		}
		return "success";
	}

}
